﻿using Microsoft.EntityFrameworkCore.Migrations;

namespace HenuAlbum.Migrations
{
    public partial class classAlbumView : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            var sql = @"
                    CREATE VIEW ClassAlbumView AS
                    SELECT Albums.Id AS AlbumId,
                           Classes.Id AS ClassId,
                           Classes.Name AS ClassName,
                           Majors.Id AS MajorId,
                           Majors.Name AS MajorName,
                           Grades.Id AS GradeId,
                           Grades.Value AS GradeValue,
                           Colleges.Id AS CollegeId,
                           Colleges.Name AS CollegeName,
                           Albums.Title, Albums.Description, Albums.Discriminator, Albums.CreateTime,
                           Albums.CreatorId, Albums.IsPublic, Albums.ReviewState
                    FROM Albums
                    INNER JOIN Classes on IFNULL(ClassId, PersonalAlbum_ClassId) = Classes.Id
                    INNER JOIN Majors on Classes.MajorId = Majors.Id
                    INNER JOIN Grades on Classes.GradeId = Grades.Id
                    INNER JOIN Colleges on Majors.CollegeId = Colleges.Id
                    WHERE (Discriminator = 'PersonalAlbum' OR Discriminator = 'GroupAlbum')
                    ORDER BY AlbumId;";

            migrationBuilder.Sql(sql);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.Sql(@"DROP VIEW ClassAlbumView");
        }
    }
}
